<?php

/*
 * Copyright (C) 2006-2009 Pham Cong Dinh
 *
 * This file is part of Pone.
 *
 * This is free software; you can redistribute it and/or modify it
 * under the terms of the GNU Lesser General Public License as
 * published by the Free Software Foundation; either version 3 of
 * the License, or (at your option) any later version.
 *
 * This software is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this software; if not, write to the Free
 * Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
 * 02110-1301 USA, or see the FSF site: http://www.fsf.org.
 */

/**
 * The object used for executing a compiled SQL statement and returning the results
 * it produces
 *
 * @category   spica
 * @package    core
 * @subpackage datasource\db\oracle
 * @author     Pham Cong Dinh <pcdinh at phpvietnam dot net>
 * @since      Version 0.1
 * @since      November 19, 2008
 * @copyright  Pham Cong Dinh (http://www.phpvietnam.net)
 * @license    http://www.gnu.org/licenses/lgpl-3.0.txt
 * @version    $Id: PreparedStatement.php 1603 2009-12-26 19:55:19Z pcdinh $
 */

/**
 * Oracle static statement
 */
require_once 'library/spica/core/datasource/db/Statement.php';

/**
 * Compiled prepared statement interface
 */
require_once 'library/spica/core/datasource/db/PreparedStatement.php';

/**
 * Note: This class can not extend from OracleStatement because
 * PHP does not support method overloading
 *
 * # native prepared statements cannot take advantage of the query cache, resulting in lower performance.
 * # native prepared statements cannot execute certains types of queries, like "SHOW TABLES"
 * # native prepared statements don't correctly communicate column lengths for certain other "SHOW" queries, resulting in garbled results.
 * # calling stored procedures multiple times using native prepared statements causes the connection to drop
 */
class SpicaOraclePreparedStatement extends SpicaOracleCommonStatement implements SpicaPreparedStatement
{
    /**
     * The OCI statement handler
     *
     * @var resource oci8 statement
     */
    protected $_stmt;

    /**
     * Named parameters
     *
     * @example array(':name' = $name);
     * @var array
     */
    protected $_parameters = array();

    /**
     * Batch mode parameters
     *
     * @var array
     */
    protected $_batchHolder = array();

    /**
     * Is the prepared statement executing a query with bind variable
     *
     * @var bool
     */
    protected $_namedParameterMode = false;

    /**
     * Binded ROWID placeholders that ared used when binding named parameters
     * into a prepared statements
     *
     * @var array
     */
    protected $_rowIds = array();

    /**
     * Binded CLOB placeholders that ared used when binding named parameters
     * into a prepared statements
     *
     * @var array
     */
    protected $_clobs = array();

    /**
     * Binded BLOB placeholders that ared used when binding named parameters
     * into a prepared statements
     *
     * @var array
     */
    protected $_blobs = array();

    /**
     * Binded OCI-Collection placeholders that ared used when binding named parameters
     * into a prepared statements
     *
     * @var array
     */
    protected $_collections = array();

    /**
     * A map from a generic data type to Oracle data type
     *
     * @var array
     */
    protected $_nativeTypeMap = array(
    'int'      => SQLT_INT,
    'string'   => SQLT_CHR, //  VARCHAR2
    'rowid'    => SQLT_RDD,
    'clob'     => SQLT_CLOB,
    'blob'     => SQLT_BLOB,
    'long'     => SQLT_LNG,
    'long_raw' => SQLT_LBI,
    'raw'      => SQLT_BIN,
    'bfile'    => SQLT_BFILEE,
    'cfile'    => SQLT_CFILEE,
    'cursor'   => SQLT_RSET,
    'double'   => SQLT_LNG,
    'collection' => SQLT_NTY
    );

    /**
     * Constructs an object of <code>SpicaOraclePreparedStatement</code>
     *
     * @param  SpicaOracleConnection $dbConn
     * @param  string $sql
     */
    public function __construct($dbConn, $sql)
    {
        $this->_dbConn    = $dbConn;
        $this->_lastQuery = $sql;
    }

    /**
     * Binds named parameters to their values
     *
     * @example
     *   $conn        = SpicaConnectionFactory::getConnection(SpicaConnectionFactory::MYSQL, $config);
     *   $sql         = 'INSERT INTO test2 (id, name, age, address) VALUES (:id, :name, :age, :address)';
     *   $pstmt       = $conn->prepareStatement($sql);
     *   $namedParams = array(':name' => $name, ':age' => array('value' => $age, 'type' => 'int', 'length' => 32), ':address' => $address);
     *   $pstmt->bindParams($namedParams);
     *   $pstmt->execute();
     *
     * @param array $namedValues array of bind values
     */
    public function bindParams($namedValues)
    {
        // Reset because they are in use in a single SQL statement
        $this->_parameters = array();

        // Normalize user assignment
        foreach ($namedValues as $name => $params)
        {
            if (false === is_string($name))
            {
                throw new SpicaDatabaseException('Unable to process bind values because you are mixing numeric parameter placeholders
                with named parameter ones. Method OraclePreparedStatement::bindParams() is used for named parameters only. ');
            }

            if (true  === is_array($params))
            {
                if (!isset($params['value']))
                {
                    $params['value'] = '';
                }

                if (!isset($params['type']))
                {
                    $params['type'] = $this->_nativeTypeMap['string'];
                }
                else
                {
                    $params['type'] = $this->_nativeTypeMap[$params['type']];
                }

                if (!isset($params['length']))
                {
                    $params['length'] = -1;
                }
            }
            else
            {
                $params  = array(
                'value'  => $params,
                'type'   => $this->_nativeTypeMap['string'],
                'length' => -1
                );
            }

            $this->_parameters[$name] = $params;
        }

        $this->_namedParameterMode = true;
    }

    /**
     * Binds values to prepared SQL statement using a given array of positional/numbered
     * bind variable values as reference for the bind variable order
     *
     * @example
     * 	  $conn   = SpicaConnectionFactory::getConnection($config);
     * 	  $sql    = 'INSERT INTO test2 (id, username) VALUES (?, ?)';
     * 	  $pstmt  = $conn->prepareStatement($sql);
     * 	  $params = array(
     * 	    0 => array('value' => 10, 'type' => 'int'),
     * 	    1 => 'pcdinh10'
     * 	  );
     * 	  $pstmt->bindValues($params);
     * 	  $pstmt->execute();
     * 	  // Free resources
     * 	  $pstmt->close();
     * 	  $conn->close();
     *
     * @param array $value array of bind values. E.x: array($name, array('type' => 'int', 'value' => $age), $address)
     */
    public function bindValues($values)
    {
        $i = 0;
        $this->_lastQuery = preg_replace('#[\?]{1}#e', '":ph".$i++', $this->_lastQuery, -1, $count);

        if ($count != count($values))
        {
            throw new SpicaDatabaseException('Unable to process bind values because the number of placeholders ('.$count.') is not equal to binding values ('.count($values).'). ');
        }

        // Reset because they are in use in a single SQL statement
        $this->_parameters     = array();

        // Normalize user assignment
        foreach ($values as $pos => $params)
        {
            if (false === is_int($pos))
            {
                throw new SpicaDatabaseException('Unable to process bind values because you are mixing numeric parameter placeholders
                with named parameter ones. Method OraclePreparedStatement::bindValues() is used for numbered bind parameters only. ');
            }

            if (true  === is_array($params))
            {
                if (!isset($params['value']))
                {
                    $params['value'] = '';
                }

                if (!isset($params['type']))
                {
                    $params['type'] = $this->_nativeTypeMap['string'];
                }
                else
                {
                    $params['type'] = $this->_nativeTypeMap[$params['type']];
                }

                if (!isset($params['length']))
                {
                    $params['length'] = -1;
                }
            }
            else
            {
                $params  = array(
                'value'  => $params,
                'type'   => $this->_nativeTypeMap['string'],
                'length' => -1
                );
            }

            $this->_parameters['ph'.$pos] = $params;
        }

        $this->_namedParameterMode = false;
    }

    /**
     * Executes the SQL statement in this <code>OraclePreparedStatement</code> object,
     * which may be any kind of SQL statement.
     *
     * Some prepared statements return multiple results; the execute method handles these complex statements
     * as well as the simpler form of statements handled by executeQuery and executeUpdate
     *
     * @see    OraclePreparedStatement::bindValues()
     * @see    OraclePreparedStatement::bindParams()
     * @throws SpicaDatabaseException
     * @return bool true if the SQL statement can return a result set;
     *              false if the SQL statement does not return a result set (INSERT/DELETE/UPDATE)
     */
    public function execute()
    {
        if (true  === $this->_isClosed)
        {
            throw new SpicaDatabaseException('Unable to execute the query because the prepared statement is closed. ');
        }

        $this->_guessQueryType();

        if (false === $this->_isSelect)
        {
            $this->executeUpdate();
            return false;
        }

        $this->executeQuery();
        return true;
    }

    /**
     * Executes the given SQL statement, which returns a single OracleResultSet object
     * like SELECT, SHOW ...
     *
     * @throws SpicaDatabaseException if a database access error occurs
     * @return OracleResultSet
     */
    public function executeQuery()
    {
        try
        {
            $this->_guessQueryType();
            $this->_doRealQueryExecution();
            return $this->getResultSet();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to execute SQL command to retrieve data. ', null, null, null, $ex);
        }
    }

    /**
     * Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement
     * or an SQL statement that returns nothing, such as an SQL DDL statement
     *
     * @throws SpicaDatabaseException if a database access error occurs
     * @return bool
     */
    public function executeUpdate()
    {
        try
        {
            $this->_isSelect = false;
            $this->_doRealQueryExecution();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to execute SQL command to manipulate the data. ', null, null, null, $ex);
        }
    }

    /**
     * Adds a set of parameters to the batch
     *
     * @throws SpicaDatabaseException if a non-result set query is specified
     */
    public function addBatch()
    {
        $this->_guessQueryType();

        if (true === $this->_isSelect)
        {
            throw new SpicaDatabaseException('Method addBatch() and executeBatch() does not support SQL commands that returns a result set. ');
        }

        $this->_batchHolder['parameters'][]      = $this->_parameters;
        $this->_batchHolder['parameter_types'][] = $this->_parameterTypes;
    }

    /**
     * Submits a batch of commands to the database for execution
     *
     * @throws SpicaDatabaseException if a database-access error occurs, or the adapter does not support batch statements
     * @throws SpicaDatabaseBatchUpdateException
     * @throws BadMethodCallException if no addBatch() call is made before
     * @return array an array of update counts containing one element for each command in the batch
     *               The array is ordered according to the order in which commands were inserted into the batch
     *               Array specs: array('affected_rows', 'last_insert_id')
     */
    public function executeBatch()
    {
        if (true  === $this->_isClosed)
        {
            throw new SpicaDatabaseException('Unable to execute the query because the prepared statement is closed. ');
        }

        if (false === empty($this->_batchHolder['parameters']))
        {
            $parameters     = $this->_batchHolder['parameters'];
            $parameterTypes = $this->_batchHolder['parameter_types'];

            $sql  = $this->_rewriteQuery();

            $stmt = $this->_createStatement($sql);

            // Batch size: number of commands
            $batchSize = count($parameters);

            // Execution results
            $rs        = array();

            for ($i    = 0; $i < $batchSize; $i++)
            {
                // Refresh parameters
                $this->_parameters     = $parameters[$i];
                $this->_parameterTypes = $parameterTypes[$i];

                // bind parameters for markers
                $this->_bindValuesToCompiledStatement();

                // execute statement
                $success  = mysqli_stmt_execute($this->_stmt);

                if (false === $success)
                {
                    throw new SpicaDatabaseBatchUpdateException('Unable to execute the compiled statement. ', mysqli_stmt_error($this->_stmt), mysqli_stmt_sqlstate($this->_stmt), mysqli_stmt_errno($this->_stmt));
                }

                $rs[] = array(
                  'affected_rows'  => mysqli_stmt_affected_rows($this->_stmt),
                  'last_insert_id' => mysqli_stmt_insert_id($this->_stmt)
                );

                // No need to to call self::closeCursor()
                // Procedural way makes sense here
                mysqli_stmt_free_result($this->_stmt);
                mysqli_stmt_reset($this->_stmt);
            }

            return $rs;
        }

        throw new BadMethodCallException('A batch of commands must be prepared with addBatch() before executeBatch() is invoked. ');
    }

    /**
     * Closes the cursor, allowing the statement to be executed again
     *
     * @return bool
     */
    public function closeCursor()
    {

    }

    /**
     * Fetches result set of multiple rows returned from SQL command as an associative array
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @param  int $caseMode The value may be one of: null,
     *                       SpicaResultSet::IDENTIFIER_LOWERCASE,
     *                       SpicaResultSet::IDENTIFIER_UPPERCASE,
     *                       SpicaResultSet::IDENTIFIER_MIXEDCASE
     * @return array An empty array is returned when there is no row found
     */
    public function fetchAll($caseMode = null)
    {
        // Check closed statement
        $this->_checkClosed();

        try
        {
            $conn = $this->_dbConn->getNativeConnection();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to execute SQL command
            to retrieve multiple-rows result set because database connection has been closed. ', null, null, null, $ex);
        }

        $this->_createStatement();

        $this->_bindParams();

        // Execute statement
        $this->_executeStatement(OCI_DEFAULT);

        $rs = array();
        $numberOfRows = oci_fetch_all($this->_stmt, $rs, 0, -1, OCI_FETCHSTATEMENT_BY_ROW+OCI_ASSOC);

        // No rows retrieved or error occurs
        if (0 === $numberOfRows || false === $numberOfRows)
        {
            return array();
        }

        // If user does not specify that field case mode
        if (null === $caseMode)
        {
            $caseMode = $this->_dbConn->getSQLIdentifierCase();
        }

        // Intentional duplicate code for performance
        if ($caseMode === SpicaResultSet::IDENTIFIER_LOWERCASE)
        {
            for ($i = 0; $i < $numberOfRows; $i++)
            {
                $rs[$i] = array_change_key_case($rs[$i], CASE_LOWER);
            }
        }
        elseif ($caseMode === SpicaResultSet::IDENTIFIER_UPPERCASE)
        {
            for ($i = 0; $i < $numberOfRows; $i++)
            {
                $rs[$i] = array_change_key_case($rs[$i], CASE_UPPER);
            }
        }

        $this->_freeLobs();
        $this->_freeRowIds();
        $this->_freeCollections();

        return $rs;
    }

    /**
     * Fetches the first column of the first row in a result set as a string
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @return string|null Null value is returned when no row is found
     */
    public function fetchOne()
    {
        // Check closed statement
        $this->_checkClosed();

        try
        {
            $conn = $this->_dbConn->getNativeConnection();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to execute SQL command
            to retrieve a single value because database connection has been closed. ', null, null, null, $ex);
        }

        // Execute statement
        $this->_executeStatement(OCI_DEFAULT);
        // Get the first row
        // Returns an indexed array with the field information, or FALSE if there are no more rows in the statement
        $row = oci_fetch_row($this->_stmt);

        if (true === isset($row[0]))
        {
            return $row[0];
        }

        return null;
    }

    /**
     * Fetches a single column from a result set as an associative array
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @return array An empty array is returned when there is no row found
     */
    public function fetchColumn()
    {
        // Check closed statement
        $this->_checkClosed();

        try
        {
            $conn = $this->_dbConn->getNativeConnection();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to execute SQL command
            to retrieve a single column result set because database connection has been closed. ', null, null, null, $ex);
        }

        // Execute statement
        $this->_executeStatement(OCI_DEFAULT);

        $rs   = array();
        $numberOfRows = oci_fetch_all($this->_stmt, $rs, 0, -1, OCI_FETCHSTATEMENT_BY_COLUMN+OCI_NUM);

        // No rows retrieved or error occurs
        if (0 === $numberOfRows || false === $numberOfRows)
        {
            return array();
        }

        return $rs[0];
    }

    /**
     * Fetches a single row from result set returned from SQL command as an associative array
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @param  int $caseMode The value may be one of: null,
     *                       SpicaResultSet::IDENTIFIER_LOWERCASE,
     *                       SpicaResultSet::IDENTIFIER_UPPERCASE,
     *                       SpicaResultSet::IDENTIFIER_MIXEDCASE
     * @return array An empty array is returned when there is no row found
     */
    public function fetchRow($caseMode = null)
    {
        // Check closed statement
        $this->_checkClosed();

        try
        {
            $conn = $this->_dbConn->getNativeConnection();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to execute SQL command
            to retrieve a single row result set because database connection has been closed. ', null, null, null, $ex);
        }

        // Execute statement
        $this->_executeStatement(OCI_DEFAULT);

        $rs   = array();
        $numberOfRows = oci_fetch_all($this->_stmt, $rs, 0, 1, OCI_FETCHSTATEMENT_BY_ROW+OCI_ASSOC);

        // No rows retrieved or error occurs
        if (0 === $numberOfRows || false === $numberOfRows)
        {
            return array();
        }

        // If user does not specify that field case mode
        if (null === $caseMode)
        {
            $caseMode = $this->_dbConn->getSQLIdentifierCase();
        }

        switch ($caseMode)
        {
            case SpicaResultSet::IDENTIFIER_LOWERCASE:
                $rs = array_change_key_case($rs[0], CASE_LOWER);
                break;

            case SpicaResultSet::IDENTIFIER_UPPERCASE:
                $rs = array_change_key_case($rs[0], CASE_UPPER);
                break;

            default:
                // no-op (present here for coding standard compliant)
        }

        return $rs;
    }

    /**
     * Retrieves certain set of rows for page navigation.
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @param  int    $pageNumber    The page sequence number
     * @param  int    $recordPerPage The number of records that will be displayed on each page
     * @param  int $caseMode The value may be one of: null,
     *                       SpicaResultSet::IDENTIFIER_LOWERCASE,
     *                       SpicaResultSet::IDENTIFIER_UPPERCASE,
     *                       SpicaResultSet::IDENTIFIER_MIXEDCASE
     * @return array
     */
    public function fetchPage($pageNumber = 1, $recordPerPage = 20, $caseMode = null)
    {
        // Calculate the ceiling limit and floor limit
        // Ceiling limit (offset): Starting index of records to fetch from
        $offset           = ($pageNumber - 1)*$recordPerPage;
        $this->_lastQuery = SpicaOracleCommand::modifyLimitQuery($this->_lastQuery, $recordPerPage, $offset);
        return $this->fetchAll($caseMode);
    }

    /**
     * Retrieves the certain number of rows counted on a given row sequence number.
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @param  int    $from The beginning row sequence number that will be retrieved, starting with 0
     * @param  int    $to   The last row sequence number that will be retrieved
     * @param  int $caseMode The value may be one of: null,
     *                       SpicaResultSet::IDENTIFIER_LOWERCASE,
     *                       SpicaResultSet::IDENTIFIER_UPPERCASE,
     *                       SpicaResultSet::IDENTIFIER_MIXEDCASE
     * @return array
     */
    public function fetchRange($from = 0, $to = 20, $caseMode = null)
    {
        if ($from > $to)
        {
            throw new SpicaDatabaseException('The higher sequence row number
            ($from:'.htmlentities($from).') can not greater than
            the lower sequence row number ($to:'.htmlentities($to).').');
        }

        $this->_lastQuery = 'SELECT * FROM (
                               SELECT sub.*, rownum AS row_num
                               FROM ('. $sql .') sub
                             )
                             WHERE row_num BETWEEN '. ($from + 1) .' AND '. $to;

        // Row at index $to is included
        return $this->fetchAll($caseMode);
    }

    /**
     * (non-PHPdoc)
     * @see trunk/incubator/Pone/Database/PreparedStatement#insert()
     */
    public function insert($tableName, $data)
    {
        throw new SpicaSQLFeatureNotSupportedException('SpicaMySQLPreparedStatement::insert() is not supported. See SpicaMySQLStatement::insert()');
    }

    /**
     * (non-PHPdoc)
     * @see trunk/incubator/Pone/Database/PreparedStatement#update()
     */
    public function update($tableName, $data, $where)
    {
        throw new SpicaSQLFeatureNotSupportedException('SpicaMySQLPreparedStatement::update() is not supported. See SpicaMySQLStatement::update()');
    }

    /**
     * (non-PHPdoc)
     * @see trunk/incubator/Pone/Database/PreparedStatement#delete()
     */
    public function delete($tableName, $where = array())
    {
        throw new SpicaSQLFeatureNotSupportedException('SpicaMySQLPreparedStatement::delete() is not supported. See SpicaMySQLStatement::delete()');
    }

    /**
     * (non-PHPdoc)
     * @see trunk/incubator/Pone/Database/PreparedStatement#replace()
     */
    public function replace($tableName, $data)
    {
        throw new SpicaSQLFeatureNotSupportedException('SpicaMySQLPreparedStatement::replace() is not supported. See SpicaMySQLStatement::replace()');
    }

    /**
     * Fetches the records from a cursor
     *
     * A cursor is a mechanism by which you can assign a name to a "select statement"
     * and manipulate the information within that SQL statement.
     *
     * FIXME
     * @param  string $sql
     * @param  array  $bind
     * @param  string $cursorPlaceholder
     * @return array
     */
    public function fetchCursor($sql, $bind = array(), $cursorPlaceholder)
    {
        $data = array();
        $conn = $this->getNativeConnection();

        $curs = oci_new_cursor($conn);
        $stmt = oci_parse($conn, $sql);

        oci_bind_by_name($stmt, ':'.$cursorPlaceholder, $curs, -1, OCI_B_CURSOR);

        foreach ($bind as $key => &$val)
        {
            oci_bind_by_name($stmt, $key, $val, -1);
        }

        if (false === oci_execute($stmt))
        {
            $err  = oci_error($stmt);
            throw new SpicaDatabaseException('Unable to execute SQL command. ', $err['message'], $err['code'], $err['code']);
        }

        if (true  === $this->_dbConn->isInTransaction())
        {
            $commitMode = OCI_DEFAULT;
        }
        else
        {
            $commitMode = OCI_COMMIT_ON_SUCCESS;
        }

        oci_execute($curs, $commitMode);

        if (oci_fetch_all($curs, $data, 0, -1, OCI_FETCHSTATEMENT_BY_ROW+OCI_ASSOC))
        {
            // FIXME
        }

        oci_free_statement($stmt);
        oci_free_cursor($curs);

        return $data;
    }

    /**
     * Returns result set from a prepared statement; this method should be called only once per result.

     * @throws SpicaDatabaseException if a database access error occurs or
     *         this method is called on a closed <code>SpicaStatement</code>
     * @see    Statement#getResultSet
     * @see    OraclePreparedStatement::_doRealQueryExecution()
     * @see    OraclePreparedStatement::executeQuery()
     *
     * @params int $caseMode The case mode of the field name
     * @return SpicaOraclePreparedStatementResultSet The current result as a <code>SpicaResultSet</code>
     */
    public function getResultSet($caseMode = null)
    {
        $case = (null === $caseMode)?$this->_dbConn->getSQLIdentifierCase():$caseMode;
        return new SpicaOraclePreparedStatementResultSet($this->_stmt, $this->_isSelect, $case);
    }

    /**
     * Executes a SQL against database server
     *
     * @throws SpicaDatabaseException
     * @return bool
     */
    protected function _doRealQueryExecution()
    {
        // Validate the SQL command and create an internal statement
        $this->_createStatement();

        $this->_bindParams();

        if (true === $this->_dbConn->isInTransaction())
        {
            $commitMode = OCI_DEFAULT;
        }
        else
        {
            $commitMode = OCI_COMMIT_ON_SUCCESS;
        }

        if (false === oci_execute($this->_stmt, $commitMode))
        {
            $this->_freeLobs();
            $this->_freeRowIds();
            $this->_freeCollections();
            $err = oci_error($stmt);
            throw new SpicaDatabaseException('Could not execute a SQL command. ', $err['message'], $err['code'], $err['code']);
        }

        $this->_freeLobs();
        $this->_freeRowIds();
        $this->_freeCollections();

        return true;
    }

    /**
     * Creates a SQL statement after validating SQL command
     *
     * @throws SpicaDatabaseException
     * @return bool true on success
     */
    protected function _createStatement()
    {
        if (true === is_resource($this->_stmt))
        {
            return true;
        }

        $this->_stmt = oci_parse($this->_dbConn->getNativeConnection(), $this->_lastQuery);

        if (false === $this->_stmt)
        {
            $err  = oci_error($this->_dbConn->getNativeConnection());
            throw new SpicaDatabaseException('Unable to parse a SQL command. ', $err['message'], $err['code'], $err['code']);
        }

        return true;
    }

    /**
     * Binds named parameters into a prepared statement actually
     *
     * @throws SpicaDatabaseException
     * @return bool true on success
     */
    protected function _bindParams()
    {
        foreach ($this->_parameters as $placeholder => &$params)
        {
            switch ($params['type'])
            {
                case SQLT_RDD: // Oracle RowID
                    $rowId    = oci_new_descriptor($this->_dbConn->getNativeConnection(), OCI_D_ROWID);

                    if (false === $rowId)
                    {
                        $err  = oci_error($this->_dbConn->getNativeConnection());
                        throw new SpicaDatabaseException('Unable to create a ROWID descriptor for placeholder named "'.$placeholder, $err['message'], $err['code'], $err['code']);
                    }

                    $rs = oci_bind_by_name($this->_stmt, $placeholder, $rowId, -1, SQLT_RDD);
                    $this->_rowIds[$placeholder] = $rowId; // OCI-Lob
                    break;

                case SQLT_BLOB:
                    $blob     = oci_new_descriptor($this->_dbConn->getNativeConnection(), OCI_D_LOB);

                    if (false === $blob)
                    {
                        $err  = oci_error($this->_dbConn->getNativeConnection());
                        throw new SpicaDatabaseException('Unable to create a BLOB descriptor for placeholder named "'.$placeholder, $err['message'], $err['code'], $err['code']);
                    }

                    $rs = oci_bind_by_name($this->_stmt, $placeholder, $blob, -1, SQLT_BLOB);
                    $this->_blobs[$placeholder] = $blob; // OCI-Lob
                    break;

                case SQLT_CLOB:
                    $clob     = oci_new_descriptor($this->_dbConn->getNativeConnection(), OCI_D_LOB);

                    if (false === $clob)
                    {
                        $err  = oci_error($this->_dbConn->getNativeConnection());
                        throw new SpicaDatabaseException('Unable to create a CLOB descriptor for placeholder named "'.$placeholder, $err['message'], $err['code'], $err['code']);
                    }

                    $rs = oci_bind_by_name($this->_stmt, $placeholder, $clob, -1, SQLT_CLOB);
                    $this->_clobs[$placeholder] = $clob; // OCI-Lob
                    break;

                case SQLT_NTY:
                    $collection = oci_new_descriptor($this->_dbConn->getNativeConnection(), SQLT_NTY);

                    /* @var $collection OCI-Collection */
                    if (false   === $collection)
                    {
                        $err    = oci_error($this->_dbConn->getNativeConnection());
                        throw new SpicaDatabaseException('Unable to create a CLOB descriptor for placeholder named "'.$placeholder, $err['message'], $err['code'], $err['code']);
                    }

                    foreach ($params['value'] as $item)
                    {
                        $collection->append($item);
                    }

                    $rs = oci_bind_by_name($this->_stmt, $placeholder, $collection, -1, OCI_B_NTY);
                    $this->_collections[$placeholder] = $collection; // OCI-Collection
                    break;

                default:
                    $rs = oci_bind_by_name($this->_stmt, $placeholder, $params['value'], $params['length'], $params['type']);
                    break;
            }

            if (false === $rs)
            {
                $this->_freeLobs();
                $this->_freeRowIds();
                $this->_freeCollections();
                $err  = oci_error($this->_connection);
                throw new SpicaDatabaseException('Can not bind the placeholder named "'.$placeholder.'" with value '.htmlspecialchars($value), $err['message'], $err['code'], $err['code']);
            }
        }
    }

    /**
     * Frees the reference to binded ROWID placeholders that ared used when binding named parameters
     * into a prepared statements
     */
    protected function _freeRowIds()
    {
        foreach ($this->_rowIds as $rowId)
        {
            $rowId->free();
        }

        $this->_rowIds = array();
    }

    /**
     * Frees the reference to binded OCI-Collection placeholders that ared used when binding named parameters
     * into a prepared statements
     */
    protected function _freeCollections()
    {
        foreach ($this->_collections as $c)
        {
            $c->free();
        }

        $this->_collections = array();
    }

    /**
     * Frees the reference to binded CLOB and BLOB placeholders that ared used when binding named parameters
     * into a prepared statements
     */
    protected function _freeLobs()
    {
        foreach ($this->_clobs as $clob)
        {
            $clob->free();
        }

        $this->_clobs = array();

        foreach ($this->_blobs as $blob)
        {
            $blob->free();
        }

        $this->_blobs = array();
    }

    /**
     * Creates a parsed statement
     *
     * @throws SpicaDatabaseException
     * @param  resource $stmt oci8 statement
     * @param  int      $commitMode Commit mode
     * @return true
     */
    protected function _executeStatement($commitMode)
    {
        $result   = oci_execute($this->_stmt, $commitMode);

        if (false === $result)
        {
            $this->_freeLobs();
            $this->_freeRowIds();
            $this->_freeCollections();
            $err  = oci_error($this->_stmt);
            throw new SpicaDatabaseException('Could not execute a SQL command. ', $err['message'], $err['code'], $err['code']);
        }

        return true;
    }

    /**
     * Closes the statement actually
     *
     * @param bool $calledExplicitly
     */
    protected function _realClose($calledExplicitly = false)
    {
        if (true === $this->_isClosed)
        {
            return true;
        }

        if (true === is_resource($this->_stmt))
        {
            return oci_free_statement($this->_stmt);
        }

        return false;
    }

    /**
     * Destroys the current object of <code>SpicaOraclePreparedStatement</code>
     *
     */
    public function __destruct()
    {
        $this->close();
    }
}

/**
 * A table of data representing a database result set, which is usually generated by
 * executing a statement that queries the database
 *
 * @category   spica
 * @package    core
 * @subpackage datasource\db\oracle
 * @author     Pham Cong Dinh <pcdinh at phpvietnam dot net>
 * @since      Version 0.1
 * @since      November 19, 2008
 * @copyright  Pham Cong Dinh (http://www.phpvietnam.net)
 * @license    http://www.gnu.org/licenses/lgpl-3.0.txt
 * @version    $Id: PreparedStatement.php 1603 2009-12-26 19:55:19Z pcdinh $
 */
class SpicaOraclePreparedStatementResultSet extends SpicaOracleCommonResultSet implements SpicaResultSet
{
    /**
     * Constructs an object of <code>SpicaOraclePreparedStatementResultSet</code>
     *
     * @param OCI resource|bool $stmt Oracle statement identifier or boolean value
     * @param bool $readQuery
     * @param bool $fieldCase
     */
    public function __construct($stmt, $readQuery, $fieldCase)
    {
        $this->_isAvailable = $readQuery;
        $this->_stmt        = $stmt;
        $this->_isSelect    = $readQuery;
        $this->_fieldCase   = $fieldCase;
    }

    /**
     * Destroys the current <code>SpicaOraclePreparedStatementResultSet</code>
     */
    public function __destruct()
    {

    }
}

?>